﻿using MyWPF.StaticData;
using System;
using System.Data;
using System.Data.SQLite;
using System.Globalization;

namespace MyWPF.Models.Journal
{
    public class JournalHelper : IJournal
    {
        public JournalHelper()
        {
            //创建数据库
            string sqlPath = @"Assets\SQLite\";
            CreateJournal(sqlPath, "Journal");
            //创建表
            string tableStr = "\"ID\" Integer PRIMARY KEY AUTOINCREMENT, \"时间\" DateTime, \"内容\" TEXT, \"类型\" TEXT, \"用户\" TEXT, \"备注\" TEXT";
            CreateTable("Journal", tableStr);
        }


        public static SQLiteConnection con_journal = new SQLiteConnection();

        public void CreateJournal(string path, string datasource)
        {
            string connectionString = "data source=" + path + datasource + ".sqlite";
            con_journal = new SQLiteConnection(connectionString);
            con_journal.Open();
            con_journal.Close();
        }

        public void CreateTable(string name, string type)
        {
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "create table if not exists " + name + "(" + type + ")";
            cmd.ExecuteNonQuery();
            con_journal.Close();
        }

        public void AddJournal(JournalContext journal)
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            if (journal.Time != null)
            {
                journal.Time = DateTime.Now;
            }
            cmd.CommandText = "insert into Journal (时间,内容,类型,用户) values(\"" + journal.Time.ToString("s") + "\",\"" +  journal.Context + "\",\"" + journal.Type + "\",\"" + journal.User + "\")";
            cmd.ExecuteNonQuery();
            con_journal.Close();

            if (journal.AddTips)
            {
                StaticModels.mainWindow.AddNews(journal.Context);
            }
        }

        public int ReadCount()
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return 0;
            }
            con_journal.Open();
            string CommandText = "select count(*)  from Journal;";
            SQLiteCommand cmd = new SQLiteCommand(CommandText, con_journal);
            int RowCount = Convert.ToInt32(cmd.ExecuteScalar());
            con_journal.Close();
            return RowCount;
        }

        public int ReadCount(string value)
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return 0;
            }
            con_journal.Open();
            string CommandText = "select count(*)  from Journal" + value +  ";";
            SQLiteCommand cmd = new SQLiteCommand(CommandText, con_journal);
            int RowCount = Convert.ToInt32(cmd.ExecuteScalar());
            con_journal.Close();
            return RowCount;
        }

        public DataTable ReadJournal()
        {

            DataTable dataTable = new DataTable();
            dataTable.Rows.Clear();
            dataTable.Columns.Add("序号", typeof(string));
            dataTable.Columns.Add("内容", typeof(string));
            dataTable.Columns.Add("类型", typeof(string));
            dataTable.Columns.Add("用户", typeof(string));
            dataTable.Columns.Add("时间", typeof(string));
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return dataTable;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "select *from Journal;";
            SQLiteDataReader reader = cmd.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {
                DataRow dr = dataTable.NewRow();
                i++;
                dr["序号"] = i.ToString();
                dr["内容"] = reader["journal"].ToString();
                dr["类型"] = reader["type"].ToString();
                dr["用户"] = reader["user"].ToString();
                dr["时间"] = reader["time"].ToString();
                dataTable.Rows.Add(dr);

            }
            reader.Close();
            con_journal.Close();
            return dataTable;
        }

        public DataTable ReadJournal(int num)
        {
            DataTable dataTable = new DataTable();
            dataTable.Rows.Clear();
            dataTable.Columns.Add("序号", typeof(string));
            dataTable.Columns.Add("内容", typeof(string));
            dataTable.Columns.Add("类型", typeof(string));
            dataTable.Columns.Add("用户", typeof(string));
            dataTable.Columns.Add("时间", typeof(string));
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return dataTable;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "select *from Journal limit 0," + num + ";";
            SQLiteDataReader reader = cmd.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {
                DataRow dr = dataTable.NewRow();
                i++;
                dr["序号"] = i.ToString();
                dr["内容"] = reader["内容"].ToString();
                dr["类型"] = reader["类型"].ToString();
                dr["用户"] = reader["用户"].ToString();
                dr["时间"] = reader["时间"].ToString();
                dataTable.Rows.Add(dr);

            }
            reader.Close();
            con_journal.Close();
            return dataTable;
        }

        public DataTable ReadJournal(int startnum, int num)
        {
            con_journal.Close();
            DataTable dataTable = new DataTable();
            dataTable.Rows.Clear();
            dataTable.Columns.Add("序号", typeof(string));
            dataTable.Columns.Add("内容", typeof(string));
            dataTable.Columns.Add("类型", typeof(string));
            dataTable.Columns.Add("用户", typeof(string));
            dataTable.Columns.Add("时间", typeof(DateTime));

            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "select *from Journal limit " + startnum + "," + num + ";";
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    DataRow dr = dataTable.NewRow();
                    dr["序号"] = reader["ID"].ToString();
                    dr["内容"] = reader["内容"].ToString();
                    dr["类型"] = reader["类型"].ToString();
                    dr["用户"] = reader["用户"].ToString();
                    //string datetime = reader.GetDateTime(2).ToString();
                    dr["时间"] = Convert.ToDateTime(reader["时间"]);
                    dataTable.Rows.Add(dr);
                }
            }

            con_journal.Close();
            return dataTable;
        }
        public DataTable ReadJournal(int startnum, int num, string value)
        {
            con_journal.Close();
            DataTable dataTable = new DataTable();
            dataTable.Rows.Clear();
            dataTable.Columns.Add("序号", typeof(string));
            dataTable.Columns.Add("内容", typeof(string));
            dataTable.Columns.Add("类型", typeof(string));
            dataTable.Columns.Add("用户", typeof(string));
            dataTable.Columns.Add("时间", typeof(DateTime));

            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "select *from Journal" + value + " limit " + startnum + "," + num + "; ";
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    DataRow dr = dataTable.NewRow();
                    dr["序号"] = reader["ID"].ToString();
                    dr["内容"] = reader["内容"].ToString();
                    dr["类型"] = reader["类型"].ToString();
                    dr["用户"] = reader["用户"].ToString();
                    //string datetime = reader.GetDateTime(2).ToString();
                    dr["时间"] = Convert.ToDateTime(reader["时间"]);
                    dataTable.Rows.Add(dr);
                }
            }

            con_journal.Close();
            return dataTable;
        }

        public bool DeleteJournal()
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return false;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "delete from Journal;";
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Close();
            con_journal.Close();
            return true;
        }

        public bool DeleteJournal(DateTime starttime, DateTime endtime)
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return false;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "delete from Journal where 时间 > '" + starttime + "' and time < '" + endtime + "';";
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Close();
            con_journal.Close();
            return true;
        }

        public bool DeleteJournal(string attribute,string value)
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return false;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "delete from Journal where " + attribute +" = '" + value + "';";
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Close();
            con_journal.Close();
            return true;
        }

        public bool DeleteJournal(string value)
        {
            if (con_journal.State != ConnectionState.Closed)
            {
                //数据库没关直接return
                return false;
            }
            con_journal.Open();
            SQLiteCommand cmd = new SQLiteCommand(con_journal);
            cmd.CommandText = "delete from Journal " + value + ";";
            SQLiteDataReader reader = cmd.ExecuteReader();
            reader.Close();
            con_journal.Close();
            return true;
        }
    }

}
